﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class CHUCVUDAO
    {
        ///CỦA AI THÌ TỰ ĐÓNG KHUNG VÀO. TRÁNH XÓA NHẦM CODE
        /////////////////CỦA LINH///////////////////
        ////////////////////////////////
        public DataTable LayDSCV()
        {
            DataTable dt = new DataTable();

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("LayDSCV", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }

        public void ThemCV(CHUCVUDTO CvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("ThemCV", conn);
            command.Parameters.Add("@TenCV", SqlDbType.NText).Value = CvDTO.TenCV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteNonQuery();
            conn.Close();
        }

        public void SuaCV(CHUCVUDTO CvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("SuaCV", conn);
            command.Parameters.Add("@MaCV", SqlDbType.Int).Value = CvDTO.MaCV;
            command.Parameters.Add("@TenCV", SqlDbType.NText).Value = CvDTO.TenCV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteNonQuery();
            conn.Close();
        }

        public int ktCV (CHUCVUDTO CvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("dbo.demNVcuaCV", conn);
            command.Parameters.Add("@MaCV", SqlDbType.Int).Value = CvDTO.MaCV;
            command.Parameters.Add("@dem", SqlDbType.Int);
            command.Parameters["@dem"].Direction = ParameterDirection.ReturnValue;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteNonQuery();
            conn.Close();
            return Convert.ToInt32(command.Parameters["@dem"].Value);
        }

        public void XoaCV(CHUCVUDTO CvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("XoaCV", conn);
            command.Parameters.Add("@MaCV", SqlDbType.Int).Value = CvDTO.MaCV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteNonQuery();
            conn.Close();
        }
        public List<CHUCVUDTO> DSChucVu()
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("DSChucVu", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataReader reader = command.ExecuteReader();
            List<CHUCVUDTO> ds = new List<CHUCVUDTO>();
            while (reader.Read())
            {
                CHUCVUDTO CvDTO = new CHUCVUDTO();
                CvDTO.MaCV = reader.GetInt32(0);
                CvDTO.TenCV = reader.GetString(1);
                ds.Add(CvDTO);
            }

            conn.Close();
            return ds;
        }

        public int ktTenCV(CHUCVUDTO CvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("dbo.ktTenCV", conn);
            command.Parameters.Add("@TenCV", SqlDbType.NText).Value = CvDTO.TenCV;
            command.Parameters.Add("@dem", SqlDbType.Int);
            command.Parameters["@dem"].Direction = ParameterDirection.ReturnValue;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteNonQuery();
            conn.Close();
            return Convert.ToInt32(command.Parameters["@dem"].Value);
        }

        public DataTable dsCVxoa()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("CVxoa", conn);
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);
            conn.Close();
            return dt;
        }
        public void phuchoiCV(CHUCVUDTO CvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("phuchoiCV", conn);
            command.Parameters.Add("@MaCV", System.Data.SqlDbType.Int).Value = CvDTO.MaCV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteNonQuery();
            conn.Close();
        }
        public void xoaCV_4ever(CHUCVUDTO CvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("XoaCV_4ever", conn);
            command.Parameters.Add("@MaCV", System.Data.SqlDbType.Int).Value = CvDTO.MaCV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteNonQuery();
            conn.Close();
        }
    }
}
